package com.fpt.quanlysanbong.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.apache.struts2.ServletActionContext;

import com.fpt.quanlyhethongsan.utils.Constants;
import com.fpt.quanlyhethongsan.utils.UserRole;
import com.fpt.quanlysanbong.domain.Account;
import com.fpt.quanlysanbong.domain.SoccerField;
import com.fpt.quanlysanbong.domain.TimeDivision;

// TODO: Auto-generated Javadoc
/**
 * The Class SoccerFieldDao.
 */
public class SoccerFieldDao extends BaseDao {

	/**
	 * Adds the soccer field.
	 * 
	 * @param soccerField
	 *            the soccer field
	 * @param timeDivisions
	 *            the time divisions
	 * @return true, if successful
	 */
	public boolean addSoccerField(SoccerField soccerField) {
		boolean success = false;

		String sql1 = "Insert into SoccerField(managerId, image_url, address, name, description, district) "
				+ "values (?,?,?,?,?,?)";
		String sql2 = "insert into TimeDivision(soccerFieldId, price, period,subSoccerField) values(?,?,?,?)";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
			prst.setInt(1, soccerField.getOwner().getId());
			prst.setString(2, soccerField.getImage_url());
			prst.setString(3, soccerField.getAddress());
			prst.setString(4, soccerField.getName());
			prst.setString(5, soccerField.getDescription());
			prst.setString(6, soccerField.getDistrict());

			int affectedRows = prst.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException("Creating SoccerField failed, no rows affected.");
			}

			rs = prst.getGeneratedKeys();
			if (rs.next()) {
				soccerField.setId(rs.getInt(1));
			} else {
				throw new SQLException("Creating SoccerField failed, no rows affected.");
			}

			for (TimeDivision time : soccerField.getListTimeDivision()) {
				prst = conn.prepareStatement(sql2);

				prst.setInt(1, soccerField.getId());
				prst.setFloat(2, time.getPrice());
				prst.setString(3, time.getPeriod());
				prst.setString(4, time.getSubSoccerField());

				prst.executeUpdate();
			}

			conn.commit();
			conn.setAutoCommit(true);
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return success;
	}

	public boolean editSoccerField(SoccerField soccerField) {
		boolean success = false;

		SoccerField sc = getSoccerFieldById(soccerField.getId());

		String sql1 = "UPDATE SoccerField SET image_url = ?, address = ?, name = ?, description = ?, district = ? WHERE id = ?";
		String sql2 = "UPDATE TimeDivision SET price = ?, period = ? , subSoccerField = ? WHERE id = ?";
		String sql3 = "DELETE FROM TimeDivision WHERE id = ?";
		String sql4 = "insert into TimeDivision(soccerFieldId, price, period,subSoccerField) values(?,?,?,?)";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setString(1, soccerField.getImage_url());
			prst.setString(2, soccerField.getAddress());
			prst.setString(3, soccerField.getName());
			prst.setString(4, soccerField.getDescription());
			prst.setString(5, soccerField.getDistrict());
			prst.setInt(6, soccerField.getId());

			prst.executeUpdate();

			
			for (int i = 0; i < sc.getListTimeDivision().size(); i++) {
				if (soccerField.getListTimeDivision().size() <= i) {
					prst = conn.prepareStatement(sql3);
					prst.setInt(1, sc.getListTimeDivision().get(i).getId());
					prst.executeUpdate();
				} else {
					prst = conn.prepareStatement(sql2);
					sc.getListTimeDivision().get(i).setPrice(soccerField.getListTimeDivision().get(i).getPrice());
					sc.getListTimeDivision().get(i).setPeriod(soccerField.getListTimeDivision().get(i).getPeriod());
					sc.getListTimeDivision().get(i).setSubSoccerField(soccerField.getListTimeDivision().get(i).getSubSoccerField());

					prst.setFloat(1, sc.getListTimeDivision().get(i).getPrice());
					prst.setString(2, sc.getListTimeDivision().get(i).getPeriod());
					prst.setString(3, sc.getListTimeDivision().get(i).getSubSoccerField());
					prst.setInt(4, sc.getListTimeDivision().get(i).getId());

					prst.executeUpdate();
				}
			}
			
			if (sc.getListTimeDivision().size() < soccerField.getListTimeDivision().size()) {
				List<TimeDivision> subtract = new ArrayList<TimeDivision>();
				
				for (int i = sc.getListTimeDivision().size(); i <soccerField.getListTimeDivision().size(); i++) {
					subtract.add(soccerField.getListTimeDivision().get(i));
				}
				
				for (TimeDivision time : subtract) {
					prst = conn.prepareStatement(sql4);

					prst.setInt(1, soccerField.getId());
					prst.setFloat(2, time.getPrice());
					prst.setString(3, time.getPeriod());
					prst.setString(4, time.getSubSoccerField());

					prst.executeUpdate();
				}
			}

			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
		
		return success;
	}

	public List<SoccerField> getListSoccerField(int managerId) {
		String sql = "Select * From SoccerField WHERE managerId = " + managerId + " order by status desc ";
		List<SoccerField> listSoccerField = new ArrayList<SoccerField>();
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				SoccerField soccerField = new SoccerField();

				soccerField.setAddress(rs.getString("address"));
				soccerField.setDelete(rs.getBoolean("delete"));
				soccerField.setDescription(rs.getString("description"));
				soccerField.setDistrict(rs.getString("district"));
				soccerField.setId(rs.getInt("id"));
				soccerField.setImage_url(rs.getString("image_url"));
				soccerField.setName(rs.getString("name"));
				soccerField.setStatus(rs.getBoolean("status"));
				soccerField.setOwner(new PersonDao().getPersonById(managerId));
				soccerField.setStaff(new PersonDao().getPersonById(rs.getInt("staffId")));
				soccerField.setListTimeDivision(getListTimeDivisionByFieldId(rs.getInt("id")));

				listSoccerField.add(soccerField);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return listSoccerField;
	}

	public SoccerField getSoccerFieldByStaff(int staffId) {
		String sql = "Select * From SoccerField WHERE staffId = " + staffId;
		SoccerField soccerField = null;
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				soccerField = new SoccerField();

				soccerField.setAddress(rs.getString("address"));
				soccerField.setDelete(rs.getBoolean("delete"));
				soccerField.setDescription(rs.getString("description"));
				soccerField.setDistrict(rs.getString("district"));
				soccerField.setId(rs.getInt("id"));
				soccerField.setImage_url(rs.getString("image_url"));
				soccerField.setName(rs.getString("name"));
				soccerField.setStatus(rs.getBoolean("status"));
				soccerField.setOwner(new PersonDao().getPersonById(rs.getInt("managerId")));
				soccerField.setStaff(new PersonDao().getPersonById(rs.getInt("staffId")));
				soccerField.setListTimeDivision(getListTimeDivisionByFieldId(rs.getInt("id")));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return soccerField;
	}

	public SoccerField getSoccerFieldById(int id) {
		String sql = "Select * From SoccerField WHERE id = " + id;
		SoccerField soccerField = null;
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				soccerField = new SoccerField();

				soccerField.setAddress(rs.getString("address"));
				soccerField.setDelete(rs.getBoolean("delete"));
				soccerField.setDescription(rs.getString("description"));
				soccerField.setDistrict(rs.getString("district"));
				soccerField.setId(rs.getInt("id"));
				soccerField.setImage_url(rs.getString("image_url"));
				soccerField.setName(rs.getString("name"));
				soccerField.setStatus(rs.getBoolean("status"));
				soccerField.setOwner(new PersonDao().getPersonById(rs.getInt("managerId")));
				soccerField.setStaff(new PersonDao().getPersonById(rs.getInt("staffId")));
				soccerField.setListTimeDivision(getListTimeDivisionByFieldId(rs.getInt("id")));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return soccerField;
	}

	public boolean deleteSoccerField(int id) {
		boolean success = false;

		String sql1 = "UPDATE SoccerField set [delete] = 1, status = 0 WHERE id = ?";

		String sql2 = "UPdate dbo.Account Set status = 0 WHERE personId = ?";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setInt(1, id);
			prst.executeUpdate();

			SoccerField sc = getSoccerFieldById(id);
			prst = conn.prepareStatement(sql2);
			prst.setInt(1, sc.getStaff() == null ? 0 : sc.getStaff().getId());
			prst.executeUpdate();

			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return success;
	}

	public List<TimeDivision> getListTimeDivisionByFieldId(int soccerFieldId) {
		String sql = "Select * From dbo.TimeDivision WHERE soccerFieldId = " + soccerFieldId + " order by id asc";
		List<TimeDivision> listTimeDivision = new ArrayList<TimeDivision>();
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				TimeDivision timeDivision = new TimeDivision();

				timeDivision.setId(rs.getInt("id"));
				timeDivision.setPeriod(rs.getString("period"));
				timeDivision.setPrice(rs.getFloat("price"));
				timeDivision.setSubSoccerField(rs.getString("subSoccerField"));

				listTimeDivision.add(timeDivision);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listTimeDivision;
	}

	public TimeDivision getTimeDivisionById(int id) {
		String sql = "Select * From dbo.TimeDivision WHERE id = " + id;
		TimeDivision td = null;
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				td = new TimeDivision();

				td.setId(rs.getInt("id"));
				td.setPeriod(rs.getString("period"));
				td.setPrice(rs.getFloat("price"));
				td.setSubSoccerField(rs.getString("subSoccerField"));
				td.setSoccerField(getSoccerFieldById(rs.getInt("soccerFieldId")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return td;
	}

	public List<SoccerField> getListSoccerField() {
		String sql = "Select * From SoccerField";
		List<SoccerField> listSoccerField = new ArrayList<SoccerField>();
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				SoccerField soccerField = new SoccerField();

				soccerField.setAddress(rs.getString("address"));
				soccerField.setDelete(rs.getBoolean("delete"));
				soccerField.setDescription(rs.getString("description"));
				soccerField.setDistrict(rs.getString("district"));
				soccerField.setId(rs.getInt("id"));
				soccerField.setImage_url(rs.getString("image_url"));
				soccerField.setName(rs.getString("name"));
				soccerField.setStatus(rs.getBoolean("status"));
				soccerField.setOwner(new PersonDao().getPersonById(rs.getInt("managerId")));
				soccerField.setStaff(new PersonDao().getPersonById(rs.getInt("staffId")));
				soccerField.setListTimeDivision(getListTimeDivisionByFieldId(rs.getInt("id")));

				listSoccerField.add(soccerField);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return listSoccerField;
	}

	public List<SoccerField> getListSoccerFieldForHome(Account acc) {
		StringBuilder sb = new StringBuilder();
		sb.append("Select * From SoccerField where [status] = 1");
		if (acc != null) {
			if (acc.getRole().getId() == 2) {
				sb.append(" and managerId = " + acc.getPerson().getId());
			} else if (acc.getRole().getId() == 4) {
				sb.append(" and staffId = " + acc.getPerson().getId());
			}
		}
		List<SoccerField> listSoccerField = new ArrayList<SoccerField>();
		try {
			ResultSet rs = conn.createStatement().executeQuery(sb.toString());
			while (rs.next()) {
				SoccerField soccerField = new SoccerField();

				soccerField.setAddress(rs.getString("address"));
				soccerField.setDelete(rs.getBoolean("delete"));
				soccerField.setDescription(rs.getString("description"));
				soccerField.setDistrict(rs.getString("district"));
				soccerField.setId(rs.getInt("id"));
				soccerField.setImage_url(rs.getString("image_url"));
				soccerField.setName(rs.getString("name"));
				soccerField.setStatus(rs.getBoolean("status"));
				soccerField.setOwner(new PersonDao().getPersonById(rs.getInt("managerId")));
				soccerField.setStaff(new PersonDao().getPersonById(rs.getInt("staffId")));
				soccerField.setListTimeDivision(getListTimeDivisionByFieldId(rs.getInt("id")));

				listSoccerField.add(soccerField);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return listSoccerField;
	}

	public boolean acceptSoccerField(int soccerFieldId) {
		boolean success = false;

		String sql1 = "UPDATE SoccerField SET status = 1 WHERE id = ?";
		String sql2 = "UPDATE dbo.Account SET roleId = " + UserRole.FIELDMANAGER.getId() + " WHERE id = ?";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setInt(1, soccerFieldId);

			prst.executeUpdate();

			prst = conn.prepareStatement(sql2);
			prst.setInt(1, getSoccerFieldById(soccerFieldId).getOwner().getId());

			prst.executeUpdate();

			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return success;
	}

	public boolean denySoccerField(int soccerFieldId) {
		boolean success = false;

		String sql1 = "UPDATE SoccerField SET status = 0, [delete] = 1 WHERE id = ?";

		try {
			conn.setAutoCommit(false);

			prst = conn.prepareStatement(sql1);
			prst.setInt(1, soccerFieldId);

			prst.executeUpdate();

			conn.commit();
			success = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return success;
	}

	public List<SoccerField> getSoccerFieldAvaiable(String keyword) {
		List<SoccerField> listSoccerField = new ArrayList<SoccerField>();
		HttpSession session = ServletActionContext.getRequest().getSession();
		Account acc = (Account) session.getAttribute(Constants.USER_SESSION);
		StringBuilder sb = new StringBuilder();
		sb.append(" select sc.*,td.id as idTimeDivision,td.period,td.price, td.subSoccerField from SoccerField sc ");
		sb.append(" inner join TimeDivision td on sc.id = td.soccerFieldId");
		sb.append(" where td.id not in (select so.timeId from SoccerOrder so where so.[status] = 1 and DATEDIFF(d, so.end_date,GETDATE()) <=0)");
		// sb.append(" where so.[status] = 1 or end_date >= GETDATE()");
		sb.append(" and sc.[status] = 1");
		if (acc != null) {
			if (acc.getRole().getId() == 4) {
				sb.append(" and sc.staffId = " + acc.getPerson().getId());
			}
		}

		if (keyword != null && !keyword.equals("")) {
			sb.append(" and (sc.name LIKE '%" + keyword + "%' or td.period LIKE '%" + keyword + "%' )");
		}

		try {
			rs = conn.createStatement().executeQuery(sb.toString());
			while (rs.next()) {
				SoccerField soccerField = new SoccerField();

				soccerField.setAddress(rs.getString("address"));
				soccerField.setDelete(rs.getBoolean("delete"));
				soccerField.setDescription(rs.getString("description"));
				soccerField.setDistrict(rs.getString("district"));
				soccerField.setId(rs.getInt("id"));
				soccerField.setImage_url(rs.getString("image_url"));
				soccerField.setName(rs.getString("name"));
				soccerField.setStatus(rs.getBoolean("status"));
				soccerField.setOwner(new PersonDao().getPersonById(rs.getInt("managerId")));
				soccerField.setStaff(new PersonDao().getPersonById(rs.getInt("staffId")));
				List<TimeDivision> listTiDivisions = new ArrayList<TimeDivision>();
				TimeDivision timeDivision = new TimeDivision();
				timeDivision.setId(rs.getInt("idTimeDivision"));
				timeDivision.setPeriod(rs.getString("period"));
				timeDivision.setPrice(rs.getFloat("price"));
				timeDivision.setSubSoccerField(rs.getString("subSoccerField"));
				listTiDivisions.add(timeDivision);
				soccerField.setListTimeDivision(listTiDivisions);

				listSoccerField.add(soccerField);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return listSoccerField;
	}

	public SoccerField getSoccerFieldByTimeDivision(int TimeDivisionId) {
		SoccerField soccerField = new SoccerField();
		StringBuilder sb = new StringBuilder();
		sb.append(" Select td.*, sf.id as idSoccerField, sf.managerId,");
		sb.append(" sf.staffId, sf.[delete], sf.image_url, sf.address, sf.name, sf.description, sf.district, sf.[status]");
		sb.append(" From TimeDivision as td");
		sb.append(" Join SoccerField as sf ON sf.id = td.soccerFieldId");
		sb.append(" Where td.id = " + TimeDivisionId);
		try {
			ResultSet rs = conn.createStatement().executeQuery(sb.toString());
			while (rs.next()) {

				soccerField.setAddress(rs.getString("address"));
				soccerField.setDelete(rs.getBoolean("delete"));
				soccerField.setDescription(rs.getString("description"));
				soccerField.setDistrict(rs.getString("district"));
				soccerField.setId(rs.getInt("idSoccerField"));
				soccerField.setImage_url(rs.getString("image_url"));
				soccerField.setName(rs.getString("name"));
				soccerField.setStatus(rs.getBoolean("status"));
				soccerField.setOwner(new PersonDao().getPersonById(rs.getInt("managerId")));
				soccerField.setStaff(new PersonDao().getPersonById(rs.getInt("staffId")));
				List<TimeDivision> listTiDivisions = new ArrayList<TimeDivision>();
				TimeDivision timeDivision = new TimeDivision();
				timeDivision.setId(rs.getInt("id"));
				timeDivision.setPeriod(rs.getString("period"));
				timeDivision.setPrice(rs.getFloat("price"));
				timeDivision.setSubSoccerField(rs.getString("subSoccerField"));
				listTiDivisions.add(timeDivision);
				soccerField.setListTimeDivision(listTiDivisions);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return soccerField;
	}
}
